package com.diapoclic.entitydao;
import java.sql.Connection;
import java.sql.ResultSet;
//import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import java.text.SimpleDateFormat;

import com.diapoclic.entitybean.Connect;
import com.diapoclic.entitybean.Document;
import com.diapoclic.entitybean.User;

public class DocumentDao {

		private UserDao userdao=new UserDao();
    	private Connect connectiondb = new Connect();
		private Connection con = connectiondb.connect();	
		private SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		
		private Document createDocument (ResultSet res) throws SQLException
		{
			Document newDocument = new Document();
			newDocument.setIdDoc(res.getInt(1));
			newDocument.setNameDoc(res.getString(2));
			newDocument.setComment(res.getString(3));
			newDocument.setSize(res.getInt(4));
			newDocument.setType(res.getString(5));
			newDocument.setPath(res.getString(6));
			newDocument.setDateAdd(res.getDate(7));
			newDocument.setUser(userdao.findById((res.getInt(8))));
			
			return newDocument;
			
		}
	
		public Connection getCon ()
		{
			return this.con;
		}
		
		public Connect getConnexiondb()
		{
			return this.connectiondb;
		}
		
		public Document findById(int id) throws SQLException
		{
			Document newDocument = new Document();
			String query;
			Statement state = con.createStatement();
			query="SELECT * FROM Document WHERE id_doc="+id;
			ResultSet result = state.executeQuery(query);
			
			if (result.next()) {
				 newDocument=createDocument(result);
				 return newDocument;
			 }
			con.close();
			return new Document();

		}
		
		public List<Document> findByIds(int[] ids) throws SQLException
		{
			List<Document> tabDocument=new ArrayList<Document>();
			String query;

			
			Statement state = con.createStatement();
			for(int i=0;i<ids.length;i++)
			{
			query="SELECT * FROM Document WHERE id_doc="+ids[i];
			ResultSet result = state.executeQuery(query);
			if (result.next()) {
				 tabDocument.add(createDocument(result));
				 
			 }
			
			}
			
			return tabDocument ;
		}
		
		public List<Document> findByIdUser(int id) throws SQLException
		{
			List<Document> tabDocument=new ArrayList<Document>();
			String query;

			
			Statement state = con.createStatement();
			query="SELECT * FROM Document WHERE id_user="+id;
			ResultSet result = state.executeQuery(query);
			while (result.next()) {
				 tabDocument.add(createDocument(result));
			 }
			
			con.close();

			return tabDocument ;
		}
		
		
		public int addDocument (String newName, String newComment, int newSize, String newType, String newPath, int newIdUser) throws SQLException
		{
			Date today=new Date();
			String query;
			Statement state = con.createStatement();
			query="INSERT INTO `diapoclic`.`Document` (`id_doc`, `name_doc`, `comment`, `size`, `type`, `path`, `date_add`, `id_user`) "; 
			query+="VALUES (NULL, '" +newName+ "', '" +newComment+ "', '" +newSize+ "', '" +newType+ "', '" +newPath+ "', '" +sdf.format(today)+ "', '" +newIdUser+ "');";
			
			
			int result = state.executeUpdate(query);
			
			con.close();

			return result;
		}
		
		public int updateDocument(Document documentUpdate) throws SQLException
		{
			String query;
			Statement state = con.createStatement();
			query="UPDATE `diapoclic`.`Document` SET";
			query+="`name_doc` = '" +documentUpdate.getNameDoc()+ "',";
			query+="`comment` = '" +documentUpdate.getComment()+ "',";
			query+="`size` = '" +documentUpdate.getSize()+ "',";
			query+="`type` = '" +documentUpdate.getType()+ "',";
			query+="`path` = '" +documentUpdate.getPath()+ "'";
			query+="WHERE `Document`.`id_doc` ="+documentUpdate.getIdDoc()+";";
			
			int result = state.executeUpdate(query);
			con.close();

			return result;
			
		}
		
		public int remove (int idDoc) throws SQLException
		{
			String query;
			Statement state = con.createStatement();
			FolderDao folderdao=new FolderDao();
			
			query="SELECT * FROM content WHERE `id_doc`="+idDoc;
			ResultSet result = state.executeQuery(query);
						
			while (result.next()) {
				//Suppression du lien du document avec les albums
				folderdao.removeDocument(result.getInt(1), idDoc);
			 }
		
			query="DELETE FROM `diapoclic`.`Document` WHERE `Document`.`id_doc` =" +idDoc + ";"; 
			
			int result2 = state.executeUpdate(query);
			System.out.println("Id Document : " + idDoc + "résultat de suppression : "+result2);

			con.close();

			return result2;
		}

		
}
